package br.com.datasun.biblioteca.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.datasun.biblioteca.dao.factory.ConnectionFactory;
import br.com.datasun.biblioteca.entities.Biblioteca;
import br.com.datasun.biblioteca.entities.Endereco;
import br.com.datasun.biblioteca.exceptions.DAOException;

public class BibliotecaDAO {
	private EnderecoDAO enderecoDAO = new EnderecoDAO();
	public List<Biblioteca> listarBibliotecasCom(String nnome) throws DAOException{
		List<Biblioteca> resultado = new ArrayList<Biblioteca>();
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT b.id ");
		sql.append("        b.nome ");
		sql.append("        b.id_endereco ");
		sql.append("   FROM biblioteca b ");
		sql.append("  WHERE b.nome like (?%) "); 
		try{
			//conecta ao banco de dados
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setString(1, nnome);
			//executa a consulta
			resultSet = preparedStatement.executeQuery();
			//gera os beans/dtos com o resultado da consulta
			while (resultSet.next()) {
				Long id = resultSet.getLong("id");
				String nome = resultSet.getString("nome");
				Endereco endereco = enderecoDAO.getEnderecoByID(resultSet.getLong("id_endereco"));
				Biblioteca biblioteca = new Biblioteca();
				biblioteca.setId(id);
				biblioteca.setNome(nome);
				biblioteca.setEndereco(endereco);
				resultado.add(biblioteca);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return resultado;
	}
	
	public boolean existeBibliotecaComONome(String nnome, long nmId) throws DAOException{
		
		boolean resultado = false;
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append("select b.id, ");
		sql.append("       b.nome ");
		sql.append("  from biblioteca b ");
		sql.append(" where b.nome like ?");
		sql.append("   and b.id <> ?"); //So Traz Registro se o id for diferente
		try{
			//conecta ao banco de dados
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setString(1, nnome);
			preparedStatement.setLong(2, nmId);
			//executa a consulta
			resultSet = preparedStatement.executeQuery();
			//gera os beans/dtos com o resultado da consulta
			if (resultSet.next()) {
				Long id = resultSet.getLong("id");
				String nome = resultSet.getString("nome");
				resultado = true;
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return resultado;
	}
	
	public List<Biblioteca> listarTodasBibliotecas() throws DAOException{
		List<Biblioteca> resultado = new ArrayList<Biblioteca>();
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet   = null;
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT b.id ");
		sql.append("       ,b.nome ");
		sql.append("       ,b.id_endereco ");
		sql.append("   FROM biblioteca b ");
		sql.append("  order by b.id "); 
		try{
			//conecta ao banco de dados
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());			
			//executa a consulta
			resultSet = preparedStatement.executeQuery();
			//gera os beans/dtos com o resultado da consulta
			while (resultSet.next()) {
				Long id = resultSet.getLong("id");
				String nome = resultSet.getString("nome");
				Endereco endereco = enderecoDAO.getEnderecoByID(resultSet.getLong("id_endereco"));
				Biblioteca biblioteca = new Biblioteca();
				biblioteca.setId(id);
				biblioteca.setNome(nome);
				biblioteca.setEndereco(endereco);
				resultado.add(biblioteca);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return resultado;
	}
	
	public boolean atualizarBiblioteca(Biblioteca biblioteca) throws DAOException{
		boolean result = false;
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		Endereco endereco = biblioteca.getEndereco();
		StringBuilder sql = new StringBuilder();
		sql.append("update biblioteca ");
		sql.append("   set nome = ?   ");
		sql.append("      ,id_endereco = ?");
		sql.append(" where id = ?");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setString(1, biblioteca.getNome());
			preparedStatement.setLong(2, endereco.getId());
			preparedStatement.setLong(3, biblioteca.getId());
			preparedStatement.executeUpdate();
			result = true;
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return result;
	}

	public Biblioteca inserir(Biblioteca biblioteca) throws DAOException{
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet = null;
		Endereco endereco = biblioteca.getEndereco();
		StringBuilder sql = new StringBuilder();
		sql.append(" insert into biblioteca (nome, id_endereco) VALUES(?,?)");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, biblioteca.getNome());
			preparedStatement.setLong(2, endereco.getId());
			preparedStatement.execute();
			resultSet = preparedStatement.getGeneratedKeys();

			if(resultSet.next()){
				Long idGerado = resultSet.getLong(1);
				biblioteca.setId(idGerado);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return biblioteca;
	}
	
	public boolean excluirBibliotecaPorId(Long idBiblioteca) throws DAOException{
		boolean result = false;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sql = new StringBuilder();
		sql.append("delete from biblioteca ");
		sql.append(" where id= ?");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setLong(1, idBiblioteca);
			preparedStatement.execute();
			result = true;
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return result;
	}

}
